﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using WebCurriculumn.BLL;
using WebCurriculumn.Modal;
using WebCurriculumn.DBAcess;
using WebCurriculumn.Helper;
using Excel = Microsoft.Office.Interop.Excel;
using System.Collections.Generic;
using WebCurriculumn.Helps;
using System.IO;

namespace WebCurriculumn
{
    public partial class CurriCulumnEdit : System.Web.UI.Page
    {
        protected bool editMode = true;
        protected string actionValue = "0";
        protected void Page_Load(object sender, EventArgs e)
        {
            if (Session["hasLogon"] != null && Session["hasLogon"].ToString() != "1")
            {
                Response.Redirect("Logon.aspx");
            }
            if (!IsPostBack)
            {
                InitPage();

            }
        }

        private void InitPage()
        {
            string subjectGradeId = Request.QueryString["subjectgradeId"];
            if (Session["LogonID"] != null)
            {
                //by zairc
                string loginCode = Session["LogonID"].ToString();
                int startIndex = MyUtils.ParseToInt(ConfigurationManager.AppSettings["loginCodeIndex"], 0);
                using (DBContext db = new DBContext())
                {
                    SqlResult result = db.CallProcedure("usp_teacher_name_getbycode", loginCode.Length > startIndex ? loginCode.Substring(startIndex) : loginCode);
                    if (result.DataSet.Tables.Count > 0 && result.DataSet.Tables[0].Rows.Count > 0)
                    {
                        DataTable dt = result.DataSet.Tables[0];
                        this.lblUserName.Text = loginCode + dt.Rows[0]["vTeacherName"].ToString();
                    }
                }
                //end
            }
            if (!string.IsNullOrEmpty(subjectGradeId))
            {
                int iSubjectGradeId = int.Parse(subjectGradeId);
                InitText(iSubjectGradeId);
                InitGrid(iSubjectGradeId);
            }
        }

        private void InitText(int iSubjectGradeId)
        {

            using (DBContext db = new DBContext())
            {
                SqlResult result = db.CallProcedure("usp_NendoSubject_get", iSubjectGradeId);
                if (result.DataSet.Tables.Count > 0 && result.DataSet.Tables[0].Rows.Count > 0)
                {
                    DataTable dt = result.DataSet.Tables[0];
                    this.lblYearValue.Text = dt.Rows[0]["iNendo"].ToString();
                    this.lblGradeValue.Text = dt.Rows[0]["iGradeProfileID"].ToString();
                    this.lblSubjectValue.Text = dt.Rows[0]["vSubjectName"].ToString();
                    ViewState["iSubjectProfileID"] = dt.Rows[0]["iSubjectProfileID"].ToString();
                }
            }

        }
        private void InitGrid(int iSubjectGradeId)
        {
            CurriCulumnModual CulumnModual = new CurriCulumnModual();
            //paras[0] = int.Parse(this.txtYear.Text);
            //paras[1] = int.Parse(this.ddlGrade.SelectedValue);
            //paras[2] = int.Parse(this.ddlSubject.SelectedValue);
            this.gvCurriCulumn.DataSource = CulumnModual.GetCurriCulumnInfoList(iSubjectGradeId);

            List<CurriCulumnEntity> tempSource = (this.gvCurriCulumn.DataSource as List<CurriCulumnEntity>);
            if (tempSource.Count > 0)
            {
                ViewState["sboFile"] = tempSource[0].SBOFile;
                ViewState["iCurriCulumnID"] = tempSource[0].CurriCulumnStatusID;
                if (tempSource[0].Status == ((int)EumnCurriLumnStatus.ＳＢＯ承認).ToString() || tempSource[0].Status == ((int)EumnCurriLumnStatus.教育指針承認).ToString() || tempSource[0].Status == ((int)EumnCurriLumnStatus.ＳＢＯ印刷済).ToString())
                {
                    this.btnSave.Enabled = false;
                    editMode = false;
                }
            }
            else
            {
                this.action.Visible = false;
            }
            this.gvCurriCulumn.DataBind();
        }

        protected void gvCurriCulumn_RowDataBound(object sender, GridViewRowEventArgs e)
        {
            if (e.Row.RowType == DataControlRowType.DataRow)
            {
                //e.Row.Cells[0].Text = (e.Row.RowIndex + 1).ToString();
                if (e.Row.FindControl("lblIndex") != null)
                {
                    (e.Row.FindControl("lblIndex") as Label).Width = 40;
                    (e.Row.FindControl("lblIndex") as Label).Text = (e.Row.DataItem as CurriCulumnEntity).Index.ToString();
                }
                if (e.Row.FindControl("lblCurriCulumnDate") != null)
                {
                    //(e.Row.FindControl("lblCurriCulumnDate") as Label).Width = 150;
                    (e.Row.FindControl("lblCurriCulumnDate") as Label).Text = (e.Row.DataItem as CurriCulumnEntity).CurriCulumnDate;
                }
                if (e.Row.FindControl("lblCurriCulumnHours") != null)
                {
                    (e.Row.FindControl("lblCurriCulumnHours") as Label).Width = 44;
                    (e.Row.FindControl("lblCurriCulumnHours") as Label).Text = (e.Row.DataItem as CurriCulumnEntity).CurriCulumnHours.ToString();
                }
                if (e.Row.FindControl("hlTeacherManage") != null)
                {
                    if (editMode)
                    {
                        (e.Row.FindControl("hlTeacherManage") as HyperLink).Text = "教員指定";
                    }
                    else
                    {
                        (e.Row.FindControl("hlTeacherManage") as HyperLink).Text = "一覧";
                    }
                    int tempId = (e.Row.DataItem as CurriCulumnEntity).ClassID;
                    (e.Row.FindControl("hlTeacherManage") as HyperLink).NavigateUrl = "CurriCulumnTeacherManage.aspx?editMode=" + editMode.ToString() + "&iClassId=" + tempId.ToString() + "&keepThis=true&TB_iframe=true&height=550&width=550";
                    ViewState["ClassID"] += "," + tempId.ToString();
                    (e.Row.FindControl("hlTeacherManage") as HyperLink).Width = 80;
                }
                if (e.Row.FindControl("ddlType") != null)
                {
                    if (editMode)
                    {
                        string type = (e.Row.DataItem as CurriCulumnEntity).CurriCulumnType;
                        (e.Row.FindControl("ddlType") as DropDownList).Text = type;
                        ViewState["typeName"] += "," + (e.Row.FindControl("ddlType") as DropDownList).UniqueID;
                    }
                    else
                    {
                        (e.Row.FindControl("ddlType") as DropDownList).Visible = false;
                    }
                }
                if (e.Row.FindControl("lblType") != null)
                {
                    //if (editMode)
                    //{
                    //    (e.Row.FindControl("lblType") as Label).Visible = false;
                    //}
                    //else
                    //{
                    string type = (e.Row.DataItem as CurriCulumnEntity).CurriCulumnType;
                    (e.Row.FindControl("lblType") as Label).Text = type;
                    (e.Row.FindControl("lblType") as Label).Width = 76;
                    //}
                }
                //if (e.Row.FindControl("txtStartTime") != null)
                //{
                //    if (editMode)
                //    {
                //        string value = (e.Row.DataItem as CurriCulumnEntity).StartTime;
                //        (e.Row.FindControl("txtStartTime") as TextBox).Text = MyUtils.FormatTime(value);
                //        ViewState["startName"] += "," + (e.Row.FindControl("txtStartTime") as TextBox).UniqueID;
                //    }
                //    else
                //    {
                //        (e.Row.FindControl("txtStartTime") as TextBox).Visible = false;
                //    }
                //}
                if (e.Row.FindControl("lblStartTime") != null)
                {
                    //if (editMode)
                    //{
                    //    (e.Row.FindControl("lblStartTime") as Label).Visible = false;
                    //}
                    //else
                    //{
                    string value = (e.Row.DataItem as CurriCulumnEntity).StartTime;
                    (e.Row.FindControl("lblStartTime") as Label).Text = MyUtils.FormatTime(value);
                    (e.Row.FindControl("lblStartTime") as Label).Width = 76;
                    //}
                }
                //if (e.Row.FindControl("txtEndTime") != null)
                //{
                //    if (editMode)
                //    {
                //        string value = (e.Row.DataItem as CurriCulumnEntity).EndTime;
                //        (e.Row.FindControl("txtEndTime") as TextBox).Text = MyUtils.FormatTime(value);
                //        ViewState["endName"] += "," + (e.Row.FindControl("txtEndTime") as TextBox).UniqueID;
                //    }
                //    else
                //    {
                //        (e.Row.FindControl("txtEndTime") as TextBox).Visible = false;
                //    }
                //}
                if (e.Row.FindControl("lblEndTime") != null)
                {
                    //if (editMode)
                    //{
                    //    (e.Row.FindControl("lblEndTime") as Label).Visible = false;
                    //}
                    //else
                    //{
                    string value = (e.Row.DataItem as CurriCulumnEntity).EndTime;
                    (e.Row.FindControl("lblEndTime") as Label).Text = MyUtils.FormatTime(value);
                    (e.Row.FindControl("lblEndTime") as Label).Width = 76;
                    //}
                }
                if (e.Row.FindControl("txtContent") != null)
                {
                    if (editMode)
                    {
                        string value = (e.Row.DataItem as CurriCulumnEntity).Content;
                        (e.Row.FindControl("txtContent") as TextBox).Text = value;
                        ViewState["contentName"] += "," + (e.Row.FindControl("txtContent") as TextBox).UniqueID;
                    }
                    else
                    {
                        (e.Row.FindControl("txtContent") as TextBox).Visible = false;
                    }
                }
                if (e.Row.FindControl("lblContent") != null)
                {
                    if (editMode)
                    {
                        (e.Row.FindControl("lblContent") as Label).Visible = false;
                    }
                    else
                    {
                        string value = (e.Row.DataItem as CurriCulumnEntity).Content;
                        (e.Row.FindControl("lblContent") as Label).Text = value;
                        (e.Row.FindControl("lblContent") as Label).Width = 220;
                    }
                }
                if (e.Row.FindControl("txtGIO") != null)
                {
                    if (editMode)
                    {
                        string value = (e.Row.DataItem as CurriCulumnEntity).GIO;
                        (e.Row.FindControl("txtGIO") as TextBox).Text = value;
                        ViewState["GIOName"] += "," + (e.Row.FindControl("txtGIO") as TextBox).UniqueID;
                    }
                    else
                    {
                        (e.Row.FindControl("txtGIO") as TextBox).Visible = false;
                    }
                }
                if (e.Row.FindControl("lblGIO") != null)
                {
                    if (editMode)
                    {
                        (e.Row.FindControl("lblGIO") as Label).Visible = false;
                    }
                    else
                    {
                        string value = (e.Row.DataItem as CurriCulumnEntity).GIO;
                        (e.Row.FindControl("lblGIO") as Label).Text = value;
                        (e.Row.FindControl("lblGIO") as Label).Width = 210;
                    }
                }
                if (e.Row.FindControl("txtSBO") != null)
                {
                    if (editMode)
                    {
                        string value = (e.Row.DataItem as CurriCulumnEntity).SBO;
                        (e.Row.FindControl("txtSBO") as TextBox).Text = value;
                        if(string.IsNullOrEmpty(value))
                        {
                            (e.Row.FindControl("txtSBO") as TextBox).Text = "1\n2\n3";
                        }
                        ViewState["SBOName"] += "," + (e.Row.FindControl("txtSBO") as TextBox).UniqueID;
                    }
                    else
                    {
                        //(e.Row.FindControl("txtSBO") as TextBox).Visible = false;
                        string value = (e.Row.DataItem as CurriCulumnEntity).SBO;
                        (e.Row.FindControl("txtSBO") as TextBox).Text = value;
                        (e.Row.FindControl("txtSBO") as TextBox).ReadOnly = true;
                    }
                }
                //if (e.Row.FindControl("lblSBO") != null)
                //{
                //    if (editMode)
                //    {
                //        (e.Row.FindControl("lblSBO") as Label).Visible = false;
                //    }
                //    else
                //    {
                //        string value = (e.Row.DataItem as CurriCulumnEntity).SBO;
                //        (e.Row.FindControl("lblSBO") as Label).Text = value;
                //        (e.Row.FindControl("lblSBO") as Label).Width = 210;
                //    }
                //}

                if (e.Row.FindControl("txtTeacher") != null)
                {
                    int tempClassID = (e.Row.DataItem as CurriCulumnEntity).ClassID;
                    if (tempClassID > 0)
                    {
                        using (DBContext db = new DBContext())
                        {
                            SqlResult result = db.CallProcedure("usp_Teacher_getByClass", tempClassID);
                            if (result.DataSet.Tables.Count > 0 && result.DataSet.Tables[0].Rows.Count > 0)
                            {
                                DataTable dt = result.DataSet.Tables[0];
                                string value = "";
                                int index = 0;
                                foreach (DataRow dr in dt.Rows)
                                {
                                    if (index == 0)
                                    {
                                        value = dr["vTeacherName"].ToString();
                                    }
                                    else
                                    {
                                        value += "\n" + dr["vTeacherName"].ToString();
                                    }
                                    index++;
                                }
                                (e.Row.FindControl("txtTeacher") as TextBox).Text = value;
                            }
                        }
                    }
                    (e.Row.FindControl("txtTeacher") as TextBox).ReadOnly = true;
                    (e.Row.FindControl("txtTeacher") as TextBox).Width = 205;
                }
                if (editMode)
                {
                    if (e.Row.FindControl("btnCopy") != null)
                    {
                        Button btn = (Button)(e.Row.FindControl("btnCopy"));
                        btn.OnClientClick = "DoCopy('" + e.Row.RowIndex.ToString() + "')";
                    }
                    if (e.Row.FindControl("btnPaste") != null)
                    {
                        Button btn = (Button)(e.Row.FindControl("btnPaste"));
                        btn.OnClientClick = "DoPaste('" + e.Row.RowIndex.ToString() + "')";
                    }
                }
            }
        }


        protected void btnPrint_Click(object sender, EventArgs e)
        {
            try
            {
                WriteCurriCulumExcel(this.lblYearValue.Text, this.lblGradeValue.Text, ViewState["iSubjectProfileID"].ToString());
                UpdateCurriCulumnStatusTable(EumnCurriLumnStatus.教育指針印刷済);
                LogHelper.WriteLog("教育指針印刷","教育指針印刷済", Session["LogonID"].ToString());
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("教育指針印刷", "教育指針印刷失敗", Session["LogonID"].ToString());
                throw ex;
            }
        }

        private void CreateExcel()
        {
            Excel.Application excel = new Excel.Application();
            Excel.Workbooks workbooks = excel.Workbooks;
            Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];
            Excel.Range range = null;
            range = (Excel.Range)worksheet.get_Range("B4", "G6");
            ((Excel.Range)worksheet.get_Range("D4", "G4")).Merge(false);
            ((Excel.Range)worksheet.get_Range("D5", "G5")).Merge(false);
            ((Excel.Range)worksheet.get_Range("D6", "G6")).Merge(false);
            range.Borders.LineStyle = 2;
            range[0, 0] = "年度";
            range[0, 1] = this.lblYearValue.Text;
            range[1, 0] = "学年";
            range[1, 1] = this.lblGradeValue.Text;
            range[2, 0] = "科目";
            range[2, 1] = this.lblSubjectValue.Text;

            List<CurriCulumnEntity> dataSource = this.gvCurriCulumn.DataSource as List<CurriCulumnEntity>;
            Excel.Range range1 = (Excel.Range)worksheet.get_Range("B8", "BB" + (dataSource.Count + 8).ToString());
            range1.AutoFit();
            range1.WrapText = true;
            range1[0, 0] = "No";
            range1[0, 1] = "日付";
            range1[0, 2] = "授業形式";
            range1[0, 3] = "時限";
            range1[0, 4] = "開始時間";
            range1[0, 5] = "終了時間";
            range1[0, 6] = "授業内容";
            for (int i = 7; i < 27; i++)
                range1[0, i] = "教員" + i.ToString();

            foreach (CurriCulumnEntity curri in dataSource)
            {
                for (int i = 1; i <= dataSource.Count; i++)
                {
                    range1[i, 0] = curri.Index.ToString();
                    range1[i, 1] = curri.CurriCulumnDate;
                    range1[i, 2] = curri.CurriCulumnType;
                    range1[i, 3] = curri.CurriCulumnHours;
                    range1[i, 4] = curri.StartTime;
                    range1[i, 5] = curri.EndTime;
                    range1[i, 6] = curri.Content;
                    ;
                    using (DBContext db = new DBContext())
                    {
                        SqlResult result = db.CallProcedure("usp_Teacher_getByClass", curri.ClassID);
                        if (result.DataSet.Tables.Count > 0 && result.DataSet.Tables[0].Rows.Count > 0)
                        {
                            DataTable dt = result.DataSet.Tables[0];
                            int startIndex = 7;
                            foreach (DataRow dr in dt.Rows)
                            {
                                range1[i, startIndex] = dr["vTeacherName"].ToString();
                                startIndex++;
                            }
                        }
                    }
                }
            }
            string fileName = ConfigurationManager.AppSettings["ExcelPath"] + DateTime.Now.ToString("yyyyMMdd") + new Random(DateTime.Now.Millisecond).Next(1000000).ToString() + ".xls";
            workbook.Saved = true;
            workbook.SaveCopyAs(fileName);
            workbooks.Close();
            KillAllExcel(excel);
            DownLoadFile(fileName);
        }

        private bool KillAllExcel(Excel.Application m_objExcel)
        {
            try
            {
                if (m_objExcel != null)
                {
                    m_objExcel.Quit();
                    System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel);
                    foreach (System.Diagnostics.Process theProc in System.Diagnostics.Process.GetProcessesByName("EXCEL"))
                    {
                        if (theProc.CloseMainWindow() == false)
                        {
                            theProc.Kill();
                        }
                    }
                    m_objExcel = null;
                    return true;
                }
            }
            catch
            {
                return false;
            }
            return true;
        }

        private void DownLoadFile(string fileName)
        {
            System.IO.FileInfo file = new System.IO.FileInfo(fileName);
            Response.Clear();
            Response.Charset = "UTF-8";
            Response.ContentEncoding = System.Text.Encoding.UTF8;
            Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));
            Response.AddHeader("Content-Length", file.Length.ToString());
            Response.ContentType = "Application/ms-excel";
            Response.WriteFile(file.FullName);
        }

        protected void btnSave_Click(object sender, EventArgs e)
        {
            string[] arrType = null;
            string[] arrStart = null;
            string[] arrEnd = null;
            string[] arrContent = null;
            string[] arrClass = null;
            string[] arrGIO = null;
            string[] arrSBO = null;
            DBContext db = new DBContext();
            try
            {
                if (ViewState["typeName"] != null)
                {
                    arrType = ViewState["typeName"].ToString().Substring(1).Split(',');
                }
                if (ViewState["startName"] != null)
                {
                    arrStart = ViewState["startName"].ToString().Substring(1).Split(',');
                }
                if (ViewState["endName"] != null)
                {
                    arrEnd = ViewState["endName"].ToString().Substring(1).Split(',');
                }
                if (ViewState["contentName"] != null)
                {
                    arrContent = ViewState["contentName"].ToString().Substring(1).Split(',');
                }
                if (ViewState["ClassID"] != null)
                {
                    arrClass = ViewState["ClassID"].ToString().Substring(1).Split(',');
                }
                if (ViewState["GIOName"] != null)
                {
                    arrGIO = ViewState["GIOName"].ToString().Substring(1).Split(',');
                }
                if (ViewState["SBOName"] != null)
                {
                    arrSBO = ViewState["SBOName"].ToString().Substring(1).Split(',');
                }
                if (arrClass.Length > 0)
                {
                    for (int i = 0; i < arrClass.Length; i++)
                    {
                        object[] paras = new object[23];
                        paras[0] = int.Parse(arrClass[i]);
                        //paras[4] = int.Parse(Request.Form[arrType[i]]);
                        //paras[9] = MyUtils.ParseToDateTime(Request.Form[arrStart[i]], DateTime.Now);
                        //paras[10] = MyUtils.ParseToDateTime(Request.Form[arrEnd[i]], DateTime.Now);
                        paras[20] = Request.Form[arrContent[i]];
                        paras[21] = Request.Form[arrGIO[i]];
                        paras[22] = Request.Form[arrSBO[i]];
                        SqlResult result = db.CallProcedure("usp_tbSTRClass_add_edit", paras);
                    }
                    UpdateCurriCulumnStatusTable(EumnCurriLumnStatus.入力中);
                    actionValue = "1";
                    LogHelper.WriteLog("一時保存", "一時保存しました", Session["LogonID"].ToString());
                }
            }
            catch (Exception ex)
            {
                LogHelper.WriteLog("一時保存", "一時保存失敗", Session["LogonID"].ToString());
                throw ex;
            }
            finally
            {
                db.Close();
            }
        }

        private string FormatTeacher(string teacherName, int activityId)
        {
            switch (activityId)
            {
                case 2:
                    teacherName = teacherName + "(非)"; break;
                case 3:
                    teacherName = teacherName + "(客)"; break;
                case 4:
                    teacherName = teacherName + "(名)"; break;
            }
            return teacherName;

        }
        private void WriteExeclRow(Excel.Worksheet xlSheet, int rowIndex, string date, string weekDayName, string timeLimit, string teacherName, string departmennt, string content)
        {
            xlSheet.Cells[rowIndex, 2] = date;
            xlSheet.Cells[rowIndex, 3] = weekDayName;
            xlSheet.Cells[rowIndex, 4] = timeLimit;
            xlSheet.Cells[rowIndex, 5] = teacherName;
            xlSheet.Cells[rowIndex, 6] = departmennt;
            xlSheet.Cells[rowIndex, 7] = content;
            //del,xzg,2012/02/14,S
            xlSheet.get_Range("B" + (rowIndex).ToString(), "G" + (rowIndex).ToString()).Borders[Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlContinuous;
            xlSheet.get_Range("B" + (rowIndex).ToString(), "G" + (rowIndex).ToString()).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlThin;
            xlSheet.get_Range("B" + (rowIndex).ToString(), "G" + (rowIndex).ToString()).Borders[Excel.XlBordersIndex.xlEdgeLeft].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
            xlSheet.get_Range("B" + (rowIndex).ToString(), "G" + (rowIndex).ToString()).Borders[Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
            xlSheet.get_Range("B" + (rowIndex).ToString(), "G" + (rowIndex).ToString()).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
            xlSheet.get_Range("B" + (rowIndex).ToString(), "G" + (rowIndex).ToString()).Borders[Excel.XlBordersIndex.xlEdgeRight].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;

            SetCellBorder(xlSheet, rowIndex);
            //del,xzg,2012/02/14,E
        }
        private void WriteCurriCulumExcel(string psNendo, string psGrade, string psKamoku)
        {
            Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            Excel.Workbook xlBook;
            Excel.Worksheet xlSheet;
            double rowHight, fixRowHeight;
            int PAGEROW;
            DataTable dt;
            rowHight = 0.0;
            string sSQL = "";
            //sSQL = sSQL + " SELECT      gp.vGradeDesc";
            sSQL = " select sp.vSubjectName1 as vSubjectName";
            sSQL = sSQL + ", sp.iSubjectProfileID,sp.vSubjectProfileCode";
            sSQL = sSQL + ", cl.iClassID ";
            sSQL = sSQL + ",convert( varchar , cl.dtPlan , 111 ) as dtPlan ";
            sSQL = sSQL + " , cl.iSerialClass";
            sSQL = sSQL + " , cl.vClassContents";
            sSQL = sSQL + ", pp.iPeriod";
            sSQL = sSQL + " , substring( convert( varchar , cl.dtStartTimePlan , 114 ) , 1 , 5 ) as dtStartTimePlan ";
            sSQL = sSQL + " , substring( convert( varchar , cl.dtEndTimePlan , 114 ) , 1 , 5 ) as dtEndTimePlan ";
            sSQL = sSQL + " , isnull( tc.vTeacherName , '' ) as vTeacherName";
            sSQL = sSQL + "  , isnull( td.vTeacherDepartmentName , '' ) as vTeacherDepartmentName";
            sSQL = sSQL + " , isnull( tc.iAuthority , 99 ) as iActivityId";
            sSQL = sSQL + " , isnull( cl.iClassTypePlanId , 99 ) as iClassTypePlanId";
            sSQL = sSQL + " FROM        dbo.tbSTRSubjectGradeProfile as sgp ";
            sSQL = sSQL + "  INNER JOIN dbo.tbSTRSubjectProfile as sp ";
            sSQL = sSQL + "               ON sgp.iSubjectProfileID = sp.iSubjectProfileID";
            sSQL = sSQL + "  INNER JOIN dbo.tbSTRSubjectScheduleProfile as ssp ";
            sSQL = sSQL + "               ON ssp.iSubjectGradeProfileID = sgp.iSubjectGradeProfileID";
            sSQL = sSQL + "  INNER Join dbo.tbSTRClass as cl ";
            sSQL = sSQL + "               ON cl.iSubjectScheduleProfileId = ssp.iSubjectScheduleProfileId";
            sSQL = sSQL + "  INNER JOIN dbo.tbSTRClassType as ct ";
            sSQL = sSQL + "               ON cl.iClassTypePlanId = ct.iClassTypeId ";
            sSQL = sSQL + "  INNER JOIN dbo.tbSTRPeriodProfile as pp ";
            sSQL = sSQL + "               ON cl.iPeriodProfileIdPlan = pp.iPeriodProfileId ";
            sSQL = sSQL + "  LEFT OUTER Join dbo.tbSTRLectureTeacher as lt ";
            sSQL = sSQL + "               ON lt.iClassId = cl.iClassId ";
            sSQL = sSQL + "  LEFT OUTER  JOIN dbo.tbSTRMapTeacherDepartment as mtd ";
            sSQL = sSQL + "               ON mtd.iMapTeacherDepartmentId = lt.iMapTeacherDepartmentId";
            sSQL = sSQL + "  LEFT OUTER  JOIN dbo.tbSTRTeacher as tc ";
            sSQL = sSQL + "               ON tc.iTeacherId = mtd.iTeacherId ";
            sSQL = sSQL + "  LEFT OUTER  JOIN dbo.tbSTRTeacherDepartment as td ";
            sSQL = sSQL + "               ON mtd.iTeacherDepartmentId = td.iTeacherDepartmentId";

            sSQL = sSQL + " WHERE       sgp.iNendo = " + psNendo;
            sSQL = sSQL + "   AND       sgp.iGradeProfileID = " + psGrade;
            sSQL = sSQL + "   AND       cl.fTrueTimePlan>0";
            sSQL = sSQL + "   AND       sgp.iSubjectProfileID in ( " + int.Parse(psKamoku) + ")";
            sSQL = sSQL + " ORDER BY ";

            sSQL = sSQL + "        dtPlan ";
            sSQL = sSQL + "        ,pp.iPeriod ";
            sSQL = sSQL + "        ,lt.iDispOrder";
            using (DBContext db = new DBContext())
            {
                SqlResult result = db.CallSqlStatement(sSQL);
                if (result.DataSet.Tables.Count > 0 && result.DataSet.Tables[0].Rows.Count > 0)
                {
                    dt = result.DataSet.Tables[0];
                }
                else
                {
                    return;
                }
            }

            xlBook = xlApp.Workbooks.Open(Server.MapPath("template") + "/template.xls", System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
            xlSheet = xlBook.ActiveSheet as Excel.Worksheet;

            //xlSheet.Name = psKamoku;
            xlApp.Visible = false;


            Excel.Worksheet GBOSOBSheet = xlBook.Worksheets[2] as Excel.Worksheet;
            DataRow dr = dt.Rows[0];
            xlSheet.Name = dr["vSubjectProfileCode"].ToString();
            //科目名
            xlSheet.Cells[3, 2] = dr["vSubjectName"].ToString();
            //学年
            xlSheet.Cells[4, 2] = psGrade + " 学年";

            //データ行出力
            string svSubjectName = dr["vSubjectName"].ToString();
            //PAGEROW = Val(gsPAGEROW)
            //If PAGEROW <= 0 Then PAGEROW = 59
            PAGEROW = int.Parse(IniFile.readIni("PAGEROW"));
            PAGEROW = 59;
            fixRowHeight = PAGEROW * 11.25;

            string strCurrentClassId;
            string strPreClassId = "";
            int startRowIndex = 7;
            string strPreDate = "";
            string strCurrentDate = "";
            string strWeekDayName = "";
            string strPreWeekDayName = "";
            string strTimeLimit = "";
            string strPreTimeLimit = "";
            string strAllTeacher = "";
            string strPreTeacher = "";
            string strAllDepartment = "";
            string strPreDepartment = "";
            string strContent = "";
            int index = -1;
            int drindex = -1;
            int startGBOStartRowIndex = 2;
            bool lastLineFlag = false;
            foreach (DataRow tempdr in dt.Rows)
            {
                strCurrentClassId = tempdr["iClassID"].ToString();
                if (strPreClassId != strCurrentClassId && index != -1)
                {
                    WriteExeclRow(xlSheet, startRowIndex + index, strCurrentDate, strWeekDayName, strTimeLimit, strAllTeacher, strAllDepartment, strContent);
                    WriteGIOSBORow(GBOSOBSheet, startGBOStartRowIndex + index * 3, strPreDate, strPreTimeLimit, strContent);
                    rowHight = rowHight + Convert.ToDouble(xlSheet.get_Range("B" + (startRowIndex + index).ToString(), "G" + (startRowIndex + index).ToString()).Height);
                    if (rowHight == fixRowHeight)
                    {
                        DrawUnderLine(xlSheet, startRowIndex + index);
                        rowHight = 0;
                    }
                    else if (rowHight > fixRowHeight)
                    {
                        DrawUnderLine(xlSheet, startRowIndex + index - 1);
                        rowHight = Convert.ToDouble(xlSheet.get_Range("B" + (startRowIndex + index).ToString(), "G" + (startRowIndex + index).ToString()).Height);
                    }
                    strPreClassId = strCurrentClassId;
                }
                else if (index != -1)
                {
                    if (strPreTeacher == tempdr["vTeacherName"].ToString())
                    {
                        strAllTeacher += "\n" + "〃";
                    }
                    else
                    {
                        strAllTeacher += "\n" + FormatTeacher(tempdr["vTeacherName"].ToString(), int.Parse(tempdr["iActivityId"].ToString()));
                        strPreTeacher = tempdr["vTeacherName"].ToString();
                    }
                    if (strPreDepartment == tempdr["vTeacherDepartmentName"].ToString())
                    {
                        strAllDepartment += "\n" + "〃";
                    }
                    else
                    {
                        strAllDepartment += "\n" + tempdr["vTeacherDepartmentName"].ToString();
                        strPreDepartment = tempdr["vTeacherDepartmentName"].ToString();
                    }
                    drindex++;
                    continue;
                }
                if (strPreClassId != strCurrentClassId) strPreClassId = strCurrentClassId;
                strCurrentDate = MyUtils.FormatDateToMd(tempdr["dtPlan"].ToString());
                if (strPreDate == strCurrentDate) strCurrentDate = "〃";
                else strPreDate = strCurrentDate;

                strWeekDayName = CommonFunction.WeekdayName(tempdr["dtPlan"].ToString());
                if (strPreWeekDayName == strWeekDayName) strWeekDayName = "〃";
                else strPreWeekDayName = strWeekDayName;

                if (int.Parse(tempdr["iClassTypePlanId"].ToString()) == 0)
                {
                    strTimeLimit = MyUtils.FormatTime(tempdr["dtStartTimePlan"].ToString()) + "～" + MyUtils.FormatTime(tempdr["dtEndTimePlan"].ToString());
                    strPreTimeLimit = "";
                }
                else
                {
                    switch (int.Parse(tempdr["iPeriod"].ToString()))
                    {
                        //時限
                        case 1:
                            strTimeLimit = "Ⅰ";  //時限"
                            break;
                        case 2:
                            strTimeLimit = "Ⅱ";  //時限"
                            break;
                        case 3:
                            strTimeLimit = "Ⅲ";   //時限"
                            break;
                        case 4:
                            strTimeLimit = "Ⅳ";   //時限"
                            break;
                        case 5:
                            strTimeLimit = "Ⅴ";   //時限"
                            break;
                        case 6:
                            strTimeLimit = "Ⅵ";  //時限"
                            break;
                        case 7:
                            strTimeLimit = "Ⅶ";  //時限"
                            break;
                        case 8:
                            strTimeLimit = "Ⅷ";   //時限"
                            break;
                        default:
                            strTimeLimit = ""; break;    //時限"
                    }
                }
                if (strPreTimeLimit == strTimeLimit) strTimeLimit = "〃";
                else strPreTimeLimit = strTimeLimit;

                strContent = tempdr["vClassContents"].ToString();
                strAllTeacher = FormatTeacher(tempdr["vTeacherName"].ToString(), int.Parse(tempdr["iActivityId"].ToString()));
                strPreTeacher = tempdr["vTeacherName"].ToString();
                strAllDepartment = tempdr["vTeacherDepartmentName"].ToString();
                strPreDepartment = tempdr["vTeacherDepartmentName"].ToString();
                index++;
                drindex++;
                if (drindex == dt.Rows.Count - 1)
                {
                    WriteExeclRow(xlSheet, startRowIndex + index, strCurrentDate, strWeekDayName, strTimeLimit, strAllTeacher, strAllDepartment, strContent);
                    WriteGIOSBORow(GBOSOBSheet, startGBOStartRowIndex + index * 3, strPreDate, strPreTimeLimit, strContent);
                    // rowHight = rowHight + Convert.ToDouble(xlSheet.get_Range("B" + startRowIndex.ToString(), "G" + startRowIndex.ToString()).Height);
                    DrawUnderLine(xlSheet, startRowIndex + index);
                    lastLineFlag = true;
                }
            }
            if (!lastLineFlag)
            {
                WriteExeclRow(xlSheet, startRowIndex + index, strCurrentDate, strWeekDayName, strTimeLimit, strAllTeacher, strAllDepartment, strContent);
                WriteGIOSBORow(GBOSOBSheet, startGBOStartRowIndex + index * 3, strPreDate, strPreTimeLimit, strContent);
                // rowHight = rowHight + Convert.ToDouble(xlSheet.get_Range("B" + startRowIndex.ToString(), "G" + startRowIndex.ToString()).Height);
                DrawUnderLine(xlSheet, startRowIndex + index);
            }

            //SetCellBorder(xlSheet, startRowIndex, startRowIndex + index);


            //最大行の処理

            //if(lRow < 1510)
            //    xlSheet.rowS(lRow + 1 + ":1510").Delete Shift:=xlUp;

            //xlSheet.range["B" + lRow + ":G" + lRow].Borders[xlEdgeBottom].LineStyle = xlContinuous;
            //xlSheet.range["B" + lRow + ":G" + lRow].Borders[xlEdgeBottom].Weight = xlThin;
            //xlSheet.range["B" + lRow + ":G" + lRow].Borders[xlEdgeBottom].ColorIndex = xlAutomatic;

            //xlSheet.PageSetup.LeftHeader = "&""ＭＳ 明朝,標準""&14" & Format(psNendo & "/01/01", "ggge年度");
            //xlSheet.PageSetup.LeftHeader = psNendo + "01/01" + psGrade + "年度";
            string fileName = ConfigurationManager.AppSettings["ExcelPath"] + DateTime.Now.ToString("yyyyMMdd") + new Random(DateTime.Now.Millisecond).Next(1000000).ToString() + ".xls";
            xlBook.Saved = true;
            xlBook.SaveCopyAs(fileName);
            KillAllExcel(xlApp);
            DownLoadFile(fileName);
        }

        private void DrawUnderLine(Excel.Worksheet xlSheet, int rowIndex)
        {
            xlSheet.get_Range("B" + rowIndex.ToString(), "G" + rowIndex.ToString()).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeBottom].LineStyle = Excel.XlLineStyle.xlContinuous;
            xlSheet.get_Range("B" + rowIndex.ToString(), "G" + rowIndex.ToString()).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlThin;
            xlSheet.get_Range("B" + rowIndex.ToString(), "G" + rowIndex.ToString()).Borders[Excel.XlBordersIndex.xlEdgeBottom].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
        }

        private void SetCellBorder(Excel.Worksheet xlSheet, int rowIndex)
        {
            Excel.Range range = xlSheet.get_Range("B" + rowIndex.ToString(), "G" + rowIndex.ToString());

            for (int i = 1; i <= range.Rows.Count; i++)
            {
                for (int j = 1; j <= range.Columns.Count; j++)
                {
                    (range.Cells[i, j] as Excel.Range).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlContinuous;
                    (range.Cells[i, j] as Excel.Range).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlThin;
                    (range.Cells[i, j] as Excel.Range).Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
                    (range.Cells[i, j] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
                    (range.Cells[i, j] as Excel.Range).VerticalAlignment = Excel.XlVAlign.xlVAlignTop;
                    if (j == range.Columns.Count) (range.Cells[i, j] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
                    (range.Cells[i, j] as Excel.Range).EntireColumn.AutoFit();
                    (range.Cells[i, j] as Excel.Range).Font.Name = "ＭＳ 明朝";
                    (range.Cells[i, j] as Excel.Range).Font.Size = 9;
                }
            }
        }

        private void SetRangeProperty(Excel.Worksheet xlSheet, int startRowIndex, int endRowIndex)
        {
            Excel.Range range = xlSheet.get_Range("B" + startRowIndex.ToString(), "G" + endRowIndex.ToString());
            range.AutoFit();
        }

        private void WriteGIOSBORow(Excel.Worksheet xlSheet, int rowIndex, string date, string timeLimit, string content)
        {
            //update,xzg,2012/02/14,S
            xlSheet.Cells[rowIndex, 1] = date;
            (xlSheet.Cells[rowIndex, 1] as Excel.Range).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
            (xlSheet.Cells[rowIndex, 1] as Excel.Range).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            (xlSheet.Cells[rowIndex, 1] as Excel.Range).Interior.ColorIndex = 34;
            (xlSheet.Cells[rowIndex, 1] as Excel.Range).Font.Name = "ＭＳ 明朝";
            (xlSheet.Cells[rowIndex, 1] as Excel.Range).Font.Size = 11;

            xlSheet.Cells[rowIndex, 2] = timeLimit;
            (xlSheet.Cells[rowIndex, 2] as Excel.Range).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
            (xlSheet.Cells[rowIndex, 2] as Excel.Range).Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            (xlSheet.Cells[rowIndex, 2] as Excel.Range).Interior.ColorIndex = 34;
            (xlSheet.Cells[rowIndex, 2] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
            (xlSheet.Cells[rowIndex, 2] as Excel.Range).Font.Name = "ＭＳ 明朝";
            (xlSheet.Cells[rowIndex, 2] as Excel.Range).Font.Size = 11;

            Excel.Range range = (Excel.Range)xlSheet.get_Range("C" + rowIndex.ToString(), "K" + rowIndex.ToString());
            range.Merge(true);
            range.Value2 = content;
            range.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, System.Drawing.Color.Black.ToArgb());
            range.Borders.get_Item(Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft).LineStyle = Excel.XlLineStyle.xlLineStyleNone;
            range.Interior.ColorIndex = 34;
            range.Font.Name = "ＭＳ 明朝";
            range.Font.Size = 11;

            xlSheet.Cells[rowIndex + 1, 1] = "ＧＩＯ：";
            (xlSheet.Cells[rowIndex + 1, 1] as Excel.Range).Interior.ColorIndex = 34;
            (xlSheet.Cells[rowIndex + 1, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            (xlSheet.Cells[rowIndex + 1, 1] as Excel.Range).Font.Name = "ＭＳ Ｐゴシック";
            (xlSheet.Cells[rowIndex + 1, 1] as Excel.Range).Font.Size = 11;
            Excel.Range range1 = (Excel.Range)xlSheet.get_Range("B" + (rowIndex + 1).ToString(), "K" + (rowIndex + 1).ToString());
            range1.Merge(true);

            xlSheet.Cells[rowIndex + 2, 1] = "SBO：";
            (xlSheet.Cells[rowIndex + 2, 1] as Excel.Range).Interior.ColorIndex = 34;
            (xlSheet.Cells[rowIndex + 2, 1] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            (xlSheet.Cells[rowIndex + 1, 1] as Excel.Range).Font.Name = "ＭＳ Ｐゴシック";
            (xlSheet.Cells[rowIndex + 1, 1] as Excel.Range).Font.Size = 11;

            xlSheet.Cells[rowIndex + 2, 2] = 1;
            (xlSheet.Cells[rowIndex + 2, 2] as Excel.Range).HorizontalAlignment = Excel.XlHAlign.xlHAlignRight;
            Excel.Range range2 = (Excel.Range)xlSheet.get_Range("C" + (rowIndex + 2).ToString(), "K" + (rowIndex + 2).ToString());
            range2.Merge(true);
            //update,xzg,2012/02/14,E

            //xzg,test,S
            //string  range = "";
            //range =rowIndex.ToString() + ":"+ (rowIndex +6).ToString() ;
            //xlSheet.Cells[rowIndex, 1]=date;
            //xlSheet.Cells[rowIndex, 2] = timeLimit;
            //xlSheet.Cells[rowIndex, 3] = content;

            // xlSheet.Rows[range].Copy();

            //string currentRow=(rowIndex+7).ToString();

            //range = currentRow + ":" + currentRow;
            //xlSheet.Rows.Insert();
            //xlSheet.Rows(range).Insert Shift:=xlDown;
            //xzg,test,E

        }

        protected void btnConfrim_Click(object sender, EventArgs e)
        {
            UpdateCurriCulumnStatusTable(EumnCurriLumnStatus.教育指針承認);
            InitPage();
            actionValue = "2";
            LogHelper.WriteLog("教育指針承認", "教育指針承認しました", Session["LogonID"].ToString());
        }

        protected void btnDownLoad_Click(object sender, EventArgs e)
        {
            if (!string.IsNullOrEmpty(ViewState["sboFile"].ToString()))
            {
                DownLoadFile(ViewState["sboFile"].ToString());
                UpdateCurriCulumnStatusTable(EumnCurriLumnStatus.ＳＢＯ印刷済);
                LogHelper.WriteLog("ダウンロード", "ダウンロードしました", Session["LogonID"].ToString());
            }
            else
            {
                LogHelper.WriteLog("ダウンロード", "ファイアがないです", Session["LogonID"].ToString());
                ClientScript.RegisterStartupScript(this.GetType(), "ok", "alert('ファイアがないです');", true);
            }

        }

        protected void btnUpload_Click(object sender, EventArgs e)
        {
            string path = ConfigurationManager.AppSettings["UpLoadPath"] + this.lblYearValue.Text + "\\" + this.lblGradeValue.Text + "\\" + ViewState["iSubjectProfileID"].ToString();
            string fileName;
            if (File.Exists(ViewState["sboFile"].ToString())) File.Delete(ViewState["sboFile"].ToString());
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            if (SBOFileUp.HasFile)
            {
                fileName = path + "\\" + this.lblYearValue.Text + this.lblGradeValue.Text + ViewState["iSubjectProfileID"].ToString() + SBOFileUp.FileName.Substring(SBOFileUp.FileName.LastIndexOf("."));
                this.SBOFileUp.SaveAs(fileName);
                ViewState["sboFile"] = fileName;
                UpdateCurriCulumnStatusTable(EumnCurriLumnStatus.ＳＢＯ承認);
                LogHelper.WriteLog("アップロード", "アップロードしました", Session["LogonID"].ToString());
                ClientScript.RegisterStartupScript(this.GetType(), "ok", "alert('アップロードしました');", true);
            }
        }

        private void UpdateCurriCulumnStatusTable(EumnCurriLumnStatus status)
        {
            int? curriculumnId;
            string user;
            string sboFile;
            if (string.IsNullOrEmpty(ViewState["iCurriCulumnID"].ToString())) curriculumnId = null;
            else curriculumnId = int.Parse(ViewState["iCurriCulumnID"].ToString());

            if (Session["loginUser"] != null) user = Session["loginUser"].ToString();
            else user = null;

            if (string.IsNullOrEmpty(ViewState["sboFile"].ToString())) sboFile = null;
            else sboFile = ViewState["sboFile"].ToString();

            object[] paras = new object[8] { curriculumnId, int.Parse(Request.QueryString["subjectgradeId"]), int.Parse(ViewState["iSubjectProfileID"].ToString()), status, user, sboFile, null, null };
            CommonFunction.UpdateStatus(paras);
        }


    }
}
